package parser;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.ArrayUtils;

public class DraftGameParser {
	private Connection connection;
	List<Team> teamList = new ArrayList<Team>();
	
	public void init() {
		establishConnection();
		populateTeams();
	}
	
	public static void main(String[] args) {
		DraftGameParser playerScoreParser = new DraftGameParser();

		playerScoreParser.init();
		//playerScoreParser.findDescrepencies();
		//playerScoreParser.insertPlayers();
		playerScoreParser.findAvailPlayers();
		//playerScoreParser.writeOutPicks();
		//playerScoreParser.updatePlayers();
		//playerScoreParser.renderAPL();
	}

	public void findDescrepencies() {
		BufferedReader bufRead2 = null;
		try {
			FileReader input2 = new FileReader("c:/file2.txt");
	        bufRead2 = new BufferedReader(input2);

	        Map<String, List<Player>> teams = getPlayerList();
			Map<String, List<Player>> teams2 = getMap();
			
			//countLists(teams);
			
			String team = null;
			String line = "";
			while ((line = bufRead2.readLine()) != null) {
				if (checkIfTeam(line)) {
					team = line;
				}
				else if (!line.equals("")) {
					List<Player> list = teams2.get(getMapKey(team, false));
					list.add(new Player(line.replace("(", "").replace(")", "").replace("*", "").trim(), getPosition(line)));
				}
			}
			
			process(teams, teams2);
			
			countLists(teams);
			countLists(teams2);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				bufRead2.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void findAvailPlayers() {
		BufferedReader bufRead2 = null;
		try {
			FileReader input2 = new FileReader("c:/file10.txt");
	        bufRead2 = new BufferedReader(input2);

	        Map<String, List<Player>> teams = getPlayerList();
			
			//countLists(teams);
			
			String line = "";
			Statement stmt = connection.createStatement();
			while ((line = bufRead2.readLine()) != null) {
				String[] items = line.split("\t");
				String surname = items[1].substring(items[1].indexOf(".") + 1, items[1].indexOf(",")).trim();
				String firstName = items[1].substring(items[1].indexOf(",") + 1);
				
				String mapKey = getMapKey(items[0].replace("[", "").replace("]", ""), true);
				String sql = "SELECT id from player where firstName = '" + firstName + "' and UPPER(surname) = '" + surname.replace("'", "''") + "' and team = '" + mapKey + "'";
				ResultSet rs = stmt.executeQuery(sql);
				
				if (rs.next()) {
					List<Player> list = teams.get(mapKey);
					Player foundPlayer = null;
					for (Player player : list) {
						if (player.getName().toUpperCase().equals(firstName.toUpperCase() + " " + surname)) {
							foundPlayer = player;
							break;
						}
					}
					if (foundPlayer != null) {
						int id = rs.getInt(1);
						System.out.println(foundPlayer.getName() + "\t" + mapKey + "\t" + items[2] + "\t" + getAverage(stmt, id) + "\t" + getCount(stmt, id));
					}
				}
				else {
					System.out.println("FAILED FOR PLAYER!!!!! playerName (" + firstName + " " + surname + ") + (" + sql + ")");
				}
			}
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				bufRead2.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	private int getAverage(Statement stmt, int id) throws Exception {
		ResultSet rs = stmt.executeQuery("select avg(score) from PlayerScore where playerId = " + id + " and score > 0");
		
		rs.next();
		
		return rs.getInt(1);
	}
	
	private int getCount(Statement stmt, int id) throws Exception {
		ResultSet rs = stmt.executeQuery("select count(score) from PlayerScore where playerId = " + id + " and score > 0");
		
		rs.next();
		
		return rs.getInt(1);
	}
	
	private boolean checkIfTeam(String line) {
		for (Team team : teamList) {
			if (team.getName().equals(line)) {
				return true;
			}
		}
		
		return false;
	}

	public void renderAPL() {
		FileWriter outFile = null;
		PrintWriter out = null;
		try {	
			outFile = new FileWriter("c:/results2.txt");
			out = new PrintWriter(outFile);
			Map<String, List<Player>> playerMap = getPlayerList();
			for (Iterator<Team> iter = teamList.iterator(); iter.hasNext();) {
				Team team = iter.next();
				List<Player> players = playerMap.get(team.getDbName());
				
				out.println("");
				out.println("[b]" + team.getName() + "[/b]");

				for (Iterator<Player> iter2 = players.iterator(); iter2.hasNext();) {
					Player player = iter2.next();
					if (player.getPosition() != null) {
						if (player.getPosition().equals("KPP")) {
							out.println("[color=blue]" + player.getName() + "[/color](**)");	
						}
						else if (player.getPosition().equals("HALF_KPP")) {
							out.println("[color=green]" + player.getName() + "[/color](*)");
						}
						else if (player.getPosition().equals("RUCK")) {
							out.println("[color=purple]" + player.getName() + "[/color](***)");
						}
						else if (player.getPosition().equals("KPP_RUCK")) {
							out.println("[color=red]" + player.getName() + "[/color](****)");
						}
					}
					else {
						out.println(player.getName());
					}
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
		out.close();
		}
	}
	
	public void insertPlayers() {
		BufferedReader bufRead = null;
		FileWriter outFile = null;
		PrintWriter out = null;
		try {
	        FileReader input = new FileReader("c:/file3.txt");
			bufRead = new BufferedReader(input);
			String line = "";
			Statement stmt = connection.createStatement();
			outFile = new FileWriter("c:/results2.txt");
			out = new PrintWriter(outFile);
			while ((line = bufRead.readLine()) != null) {
				if (line != null && !line.equals("")) line = line.trim();
				if (!line.equals("") && !line.startsWith("Round")) {
					line = line.replace(" #", "");
					String coach = getCoach(line.substring(line.indexOf(" ") + 1, line.indexOf(" -")).trim());
					String playerName = line.substring(line.indexOf("-") + 2, line.lastIndexOf(" ")).replace("*", "").replace("(", "").replace(")", "").trim();
					String team = line.substring(line.indexOf("[") + 1, line.indexOf("]"));
					int coachId = -1, playerId = -1;
					boolean failed = false;
					
					ResultSet rs = stmt.executeQuery("SELECT id from team where coachName = '" + coach.replace("'", "''") + "'");
					
					if (rs.next()) {
						coachId = rs.getInt("id");
					}
					else {
						System.out.println("FAILED FOR COACH!!!!!");
						failed = true;
					}
					
					String sql = "SELECT id from player where firstName = '" + playerName.substring(0, playerName.indexOf(" ")) + "' and surname = '" + playerName.substring(playerName.indexOf(" ") + 1).replace("'", "''") + "' and team = '" + getMapKey(team, true) + "'";
					rs = stmt.executeQuery(sql);
					
					if (rs.next()) {
						playerId = rs.getInt("id");
					}
					else {
						System.out.println("FAILED FOR PLAYER!!!!! playerName (" + playerName + ")(" + sql + ")");
						failed = true;
					}
					
					if (!failed) {
						System.out.println("INSERT INTO TeamPlayer (playerId, teamId) values (" + playerId + "," + coachId + ");");
					}
					else {
						System.out.println("FAILED!!!!! for - " + line);
					}
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				out.close();
				bufRead.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void writeOutPicks() {
		String[] names = new String[] {"goodesy", "BBB", "Jezza", "Top Gun", "Charlie", "Josh's Heroes", "BM", "JB", "gannon", "AlexisCats408", "Aidans Blues", "B4F", "RichoMan", "Little Blue Boy", "Bradles", "Querty"};
		String[] names2  = new String[] {"Querty", "Bradles", "Little Blue Boy", "RichoMan", "B4F", "Aidans Blues", "AlexisCats408", "gannon", "JB", "BM", "Josh's Heroes", "Charlie", "Top Gun", "Jezza", "BBB", "goodesy"};
		FileWriter outFile = null;
		PrintWriter out = null;
		try {
			outFile = new FileWriter("c:/results5.txt");
			out = new PrintWriter(outFile);
			
			for (int i = 11; i <= 30; i++) {
				out.write("\n[b]Round " + i + "[/b]\n");
				
				if (i == 29) {
					forwards(names2, out, i);
				}
				else if (i == 30) {
					forwards(names, out, i);
				}
				else if (i%2 == 0) {
					forwards(names2, out, i);
				}
				else {
					forwards(names, out, i);
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				out.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void updatePlayers() {
		BufferedReader bufRead = null;
		FileWriter outFile = null;
		PrintWriter out = null;
		try {
	        FileReader input = new FileReader("c:/file.txt");
			bufRead = new BufferedReader(input);
			String line = "";
			Statement stmt = connection.createStatement();
			outFile = new FileWriter("c:/results2.txt");
			out = new PrintWriter(outFile);
			String team = "";
			
			String firstName = "";
			String surname = "";
			int startIndex = 0;
			int endIndex = 0;
			String name = "";
			List<String> players = new ArrayList<String>();
			while ((line = bufRead.readLine()) != null) {
				String playerType = null;
				if (line.equals("")) {
					//blah
				}
				else if (line.startsWith("[b]")) {
					team = line.replace("[b]", "").replace("[/b]", "");
				}
				else {
					if (line.startsWith("[color=purple]")) {
						playerType = "RUCK";
					}
					else if (line.startsWith("[color=blue]")) {
						playerType = "KPP";
					}
					else if (line.startsWith("[color=red]")) {
						playerType = "KPP_RUCK";
					}
					else if (line.startsWith("[color=green]")) {
						playerType = "HALF_KPP";
					}
					
					if (line.indexOf(" - ") != -1) {
						line = line.substring(0, line.indexOf(" - "));
					}
					if (line.indexOf("]") != -1) {
						startIndex = line.indexOf("]") + 1;
						endIndex = line.indexOf("[/");
						
						name = line.substring(startIndex, endIndex);
					}
					else {
						name = line;
					}
	
					//System.out.println("name - " + name);
					firstName = name.substring(0, name.indexOf(" "));
					surname = name.substring(name.indexOf(" ") + 1).replace("'", "''");
					
					ResultSet rs = stmt.executeQuery( "SELECT * FROM Player where firstName = '" + firstName + "' and surname = '" + surname + "'");
					
					if (rs.next()) {
						String oldPlayerType = rs.getString("playerType");
						String oldTeam = rs.getString("team");
						String oldTeam2 = null;
						int id = rs.getInt("id");
						if (rs.next()) {
							oldTeam2 = rs.getString("team");
							if (team.equals(oldTeam2)) {
								oldPlayerType = rs.getString("playerType");
								id = rs.getInt("id");
								oldTeam = oldTeam2;
							}
						}
						
						if (!team.equals(oldTeam)) {
							if (!team.equals(oldTeam2)) {
								out.write("UPDATE Player set team = '" + team + "' where id = " + id + ";\n");
								System.out.println("Changing team for " + name + " from " + oldTeam + " to " + team);
							}
						}
						if (oldPlayerType == null && playerType != null) {
							out.write("UPDATE Player set playerType = '" + playerType + "' where id = " + id + ";\n");
							System.out.println("Adding playerType for " + name + " of " + playerType);
						}
						else if (oldPlayerType != null && playerType == null) {
							out.write("UPDATE Player set playerType = null where id = " + id + ";\n");
							System.out.println("Removing playerType for " + name);
						}
						else if (oldPlayerType != null && !oldPlayerType.equals(playerType)) {
							out.write("UPDATE Player set playerType = '" + playerType + "' where id = " + id + ";\n");
							System.out.println("Updating playerType for " + name + " from " + oldPlayerType + " to " + playerType);
						}
					}
					else {
						out.write("INSERT INTO Player (firstName, surname, playerType, team) VALUES ('" + firstName + "', '" + surname + "', " + ((playerType==null)?"null":("'" + playerType + "'")) + ", '" + team + "');\n");
						System.out.println("Inserting player " + name);
					}
					
					players.add(name);
				}
			}
			
			ResultSet rs2 = stmt.executeQuery( "SELECT * FROM Player");
			String[] playerArray = players.toArray(new String[0]);
			
			while (rs2.next()) {
				String playerName = rs2.getString("firstName") + " " + rs2.getString("surname");
				int id = rs2.getInt("id");
				if (!ArrayUtils.contains(playerArray, playerName)) {
					out.write("DELETE FROM PlayerScore where playerid = " + id + ";\n");
					out.write("DELETE FROM Player where id = " + id + ";\n");
					System.out.println("Deleting player " + playerName);
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				out.close();
				bufRead.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	private Map<String, List<Player>> getPlayerList() {
		BufferedReader bufRead = null;
		Map<String, List<Player>> teams = getMap();
		
		try {
	        FileReader input = new FileReader("c:/file.txt");
	        bufRead = new BufferedReader(input);
			
			String line = "";
			Statement stmt = connection.createStatement();
			
			ResultSet rs = stmt.executeQuery("select * from player order by team, surname, firstname");
			
			while (rs.next()) {
				List<Player> list = teams.get(rs.getString("team"));
				list.add(new Player(rs.getString("firstName") + " " + rs.getString("surname"), rs.getString("playerType")));
			}
			
			//countLists(teams);
			
			while ((line = bufRead.readLine()) != null) {
				if (line != null) {
					line = line.trim();
				}
				if (!line.equals("") && !line.startsWith("Round")) {
					String team = line.substring(line.indexOf("[") + 1).replace("]", "").trim();
					List<Player> list = teams.get(getMapKey(team, true));
					String name = getPlayerName(line);
					boolean foundIt = false;
					for (Iterator<Player> iter = list.iterator(); iter.hasNext();) {
						Player player = iter.next();
						if (player.getName().equals(name)) {
							list.remove(player);
							foundIt = true;
							break;
						}
					}
					if (!foundIt) {
						System.out.println("ERROR - " + name + ", " + team);
					}
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				bufRead.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return teams;
	}

	private String getPlayerName(String line) {
		if (line.indexOf("- ") != -1) {
			return line.substring(line.indexOf("-") + 2, line.lastIndexOf(" ")).replace("(", "").replace(")", "").replace("*", "").trim();
		}
		else {
			return line.substring(line.indexOf("-") + 1, line.lastIndexOf(" ")).replace("(", "").replace(")", "").replace("*", "").trim();
		}
	}
	
	private void forwards(String[] names2, PrintWriter out, int i) {
		for (int j = 0; j < names2.length; j++) {
			out.write("#" + (160 + (j + 1) + (16 * (i - 11))) + " " + names2[j] + " -\n");
		}
	}

	private Map<String, List<Player>> getMap() {
		Map<String, List<Player>> teams = new HashMap<String, List<Player>>();
		teams.put("GC", new ArrayList<Player>());
		teams.put("NORTH_MELBOURNE", new ArrayList<Player>());
		teams.put("BRISBANE", new ArrayList<Player>());
		teams.put("WESTERN_BULLDOGS", new ArrayList<Player>());
		teams.put("CARLTON", new ArrayList<Player>());
		teams.put("ST_KILDA", new ArrayList<Player>());
		teams.put("ADELAIDE", new ArrayList<Player>());
		teams.put("HAWTHORN", new ArrayList<Player>());
		teams.put("COLLINGWOOD", new ArrayList<Player>());
		teams.put("GEELONG", new ArrayList<Player>());
		teams.put("MELBOURNE", new ArrayList<Player>());
		teams.put("ESSENDON", new ArrayList<Player>());
		teams.put("SYDNEY", new ArrayList<Player>());
		teams.put("PORT_ADELAIDE", new ArrayList<Player>());
		teams.put("FREMANTLE", new ArrayList<Player>());
		teams.put("WCE", new ArrayList<Player>());
		teams.put("RICHMOND", new ArrayList<Player>());
		
		return teams;
	}
	
	private String getCoach(String name) {
		if (name.equals("AlexisCats408")) return "al3x";
		else if (name.equals("Little Blue Boy")) return "LBB";
		else if (name.equals("Aidans Blues")) return "AB";
		else if (name.equals("Josh' Heroes")) return "Josh's Heroes";
		else if (name.equals("querty") || name.equals("Querty")) return "qwerty";
		else if (name.equals("*Lotto and Liverpoolfc")) return "LottoLiver";
		else if (name.equals("The Succession Plan")) return "jeffLBBBarney";
		else if (name.indexOf("black mambas") != -1) return "TBDoggiesEJ";
		else if (name.equals("*scrote and mallia")) return "scrotemallia";
		else if (name.equals("Evolutions Revolution")) return "neilTG";
		else if (name.equals("The Navy Lions")) return "GBJB";
		else if (name.equals("The Clever Idiots")) return "spenceFG";
		else if (name.equals("Just ripe")) return "JeffersonBradles";
		else if (name.equals("The Black And Blue Batsquirrel")) return "BMbluebaggerBats";
		else if (name.equals("*gannon233 and Vass")) return "gannonVass";
		else if (name.equals("Decreasing Evoloution")) return "qwertyAl3x";
		else if (name.equals("mASHo")) return "masoAsh";
		else if (name.equals("It's All Skill")) return "CoopMacro";
		else if (name.equals("Darwin FC")) return "BSAB";
		else if (name.equals("The Jerky McJerks United")) return "BovSt00pRojik";
		else if (name.equals("Goodsey's Heroes")) return "goodseyJH";
		else return name;
	}

	private void process(Map<String, List<Player>> teams,
			Map<String, List<Player>> teams2) {
		for (Iterator<String> iter = teams.keySet().iterator(); iter.hasNext();) {
			String key = iter.next();
			
			List<Player> players1 = teams.get(key);
			List<Player> players2 = teams2.get(key);
			
			findPlayers(players1, players2, true);
			findPlayers(players2, players1, false);
		}
	}

	private void findPlayers(List<Player> players1, List<Player> players2, boolean first) {
		for (Iterator<Player> iter2 = players1.iterator(); iter2.hasNext();) {
			Player playerOne = iter2.next();
			
			boolean foundIt = false;
			for (Iterator<Player> iter8 = players2.iterator(); iter8.hasNext();) {
				Player player = iter8.next();
				if (player.getName().equals(playerOne.getName())) {
					foundIt = true;
					break;
				}
			}
			if (!foundIt) {
				System.out.println(playerOne.getName() + ((first)?" in list 1 but not list 2":" in list 2 but not list 1"));
			}
			
		}
	}
	
	private String getPosition(String line) {
		if (line.indexOf("(*)") != -1) {
			return "HALF_KPP";
		}
		else if (line.indexOf("(**)") != -1) {
			return "KPP";
		}
		else if (line.indexOf("(***)") != -1) {
			return "RUCK";
		}
		else if (line.indexOf("(****)") != -1) {
			return "KPP_RUCK";
		}
		else {
			return null;
		}
	}	

	private void countLists(Map<String, List<Player>> teams) {
		int total = 0;
		for (Iterator iter = teams.keySet().iterator(); iter.hasNext();) {
			String key = (String) iter.next();
			List<Player> players = teams.get(key);
			
			total += players.size();
			//System.out.println(key + " - " + players.size());
			
			for (Iterator<Player> iter2 = players.iterator(); iter2.hasNext();) {
				Player player = iter2.next();
				System.out.println(player.getName() + "\t" + player.getPosition());
			}
		}
		//System.out.println(total + " - " + total);
	}
	
	private String getMapKey(String name, boolean useShortName) {
		
		for (Iterator<Team> iter = teamList.iterator(); iter.hasNext();) {
			Team team = iter.next();
			if (useShortName) {
				for (int i = 0; i < team.getShortName().length; i++) {
					if (team.getShortName()[i].equals(name)) {
						return team.getDbName();						
					}
				}
			}
			else if (team.getName().equals(name)) {
				return team.getDbName();
			}
		}
		
		throw new RuntimeException("No team found for - " + name);
		
	}
	
	private void populateTeams() {
		teamList.add(new Team("ADELAIDE", new String[] {"ADEL", "ADL"}, "Adelaide"));
		teamList.add(new Team("BRISBANE", new String[] {"BRIS"}, "Brisbane"));
		teamList.add(new Team("CARLTON", new String[] {"CARL"}, "Carlton"));
		teamList.add(new Team("COLLINGWOOD", new String[] {"COLL", "COL"}, "Collingwood"));
		teamList.add(new Team("ESSENDON", new String[] {"ESS"}, "Essendon"));
		teamList.add(new Team("FREMANTLE", new String[] {"FREM", "FREO", "FRE"}, "Fremantle"));
		teamList.add(new Team("GC", new String[] {"GC"}, "Gold Coast"));
		teamList.add(new Team("GEELONG", new String[] {"GEEL", "GEL"}, "Geelong"));
		teamList.add(new Team("HAWTHORN", new String[] {"HAW", "HAWK"}, "Hawthorn"));
		teamList.add(new Team("MELBOURNE", new String[] {"MELB"}, "Melbourne"));
		teamList.add(new Team("NORTH_MELBOURNE", new String[] {"NTH", "KANG"}, "North Melbourne"));
		teamList.add(new Team("PORT_ADELAIDE", new String[] {"PORT", "PA"}, "Port Adelaide"));
		teamList.add(new Team("RICHMOND", new String[] {"RICH"}, "Richmond"));
		teamList.add(new Team("ST_KILDA", new String[] {"STK"}, "St Kilda"));
		teamList.add(new Team("SYDNEY", new String[] {"SYD"}, "Sydney"));
		teamList.add(new Team("WCE", new String[] {"WCE"}, "West Coast"));
		teamList.add(new Team("WESTERN_BULLDOGS", new String[] {"WBD", "DOGS"}, "Western Bulldogs"));
	}
	
	class Team {
		private String dbName;
		private String[] shortName;
		private String name;

		public Team(String dbName, String[] shortName, String name) {
			super();
			this.dbName = dbName;
			this.shortName = shortName;
			this.name = name;
		}
		public String getDbName() {
			return dbName;
		}
		public void setDbName(String dbName) {
			this.dbName = dbName;
		}
		public String[] getShortName() {
			return shortName;
		}
		public void setShortName(String[] shortName) {
			this.shortName = shortName;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
	}
	
	class Player {
		private String name;
		private String position;

		public Player(String name, String position) {
			this.name = name;
			this.position = position;
		}
		
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getPosition() {
			return position;
		}
		public void setPosition(String position) {
			this.position = position;
		}
	}
	
	private void establishConnection() {
		try {
			Class.forName( "com.mysql.jdbc.Driver" );
			connection = DriverManager.getConnection(
				     "jdbc:mysql://localhost/sffdraftgame",
				     "root",
				     "password" );

		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}
